package com.bolt.support.codegenerator.db;

import com.bolt.common.utils.StrUtil;
import com.bolt.convention.data.DefaultPageInfo;
import com.bolt.convention.data.PageInfo;
import com.bolt.support.codegenerator.dto.TableInfo;
import com.bolt.support.sqlconfig.dialect.PagingPostgreSqlDialect;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SqlServerTableFactory extends AbstractTableFactory {

    private static final String TABLE_COMMENTS_SQL = "SELECT DISTINCT CAST(d.name AS varchar(500)) Name,CAST(f.value AS varchar(500)) Comment FROM syscolumns a\nLEFT JOIN systypes b ON a.xusertype= b.xusertype\nINNER JOIN sysobjects d ON a.id= d.id\nAND d.xtype= \'U\'\nAND d.name<> \'dtproperties\'\nLEFT JOIN syscomments e ON a.cdefault= e.id\nLEFT JOIN sys.extended_properties g ON a.id= G.major_id\nAND a.colid= g.minor_id\nLEFT JOIN sys.extended_properties f ON d.id= f.major_id\nAND f.minor_id= 0 ";

    public SqlServerTableFactory(String url, String username, String password, String driverClass) {
        super(url, username, password, driverClass);
    }


    @Override
    public PageInfo<TableInfo> getTables(String tableName, int page, int size) throws SQLException {
        String sql = "SELECT DISTINCT CAST(d.name AS varchar(500)) Name,CAST(f.value AS varchar(500)) Comment FROM syscolumns a\nLEFT JOIN systypes b ON a.xusertype= b.xusertype INNER JOIN sysobjects d ON a.id= d.id AND d.xtype= \'U\' AND d.name<> \'dtproperties\' LEFT JOIN syscomments e ON a.cdefault= e.id LEFT JOIN sys.extended_properties g ON a.id= G.major_id AND a.colid= g.minor_id LEFT JOIN sys.extended_properties f ON d.id= f.major_id AND f.minor_id= 0 ;";

        String totalSql = "select COUNT(*) FROM pg_tables A, pg_class B WHERE A.schemaname='public' AND A.tablename = B.relname and A.tablename like ?";

        PagingPostgreSqlDialect dialect = new PagingPostgreSqlDialect();

        String pageSql = dialect.getLimitString(sql,page,size);
        List <TableInfo> datas = new ArrayList<>();
        PreparedStatement pstmt = null;
        PreparedStatement countStmt = null;
        long total = 0;
        try {
            pstmt = getConnection().prepareStatement(pageSql);
            pstmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");

            ResultSet rs = pstmt.executeQuery();//执行查询操作
            while (rs.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(rs.getString("table_name"));
                tableInfo.setRemark(rs.getString("table_comment"));
                datas.add(tableInfo);
            }
            countStmt = getConnection().prepareStatement(totalSql);
            countStmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");
            ResultSet count = countStmt.executeQuery();
            if (count.next()) {
                total = count.getLong(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pstmt.close();
        }
        return new DefaultPageInfo(page, size, datas, total);
    }
}